package com.example.unique.database
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.database.Cursor

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, "User.db", null, 1) {

    override fun onCreate(db: SQLiteDatabase) {
        val createTable = """
            CREATE TABLE User (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                academy TEXT,
                club TEXT,
                name TEXT,
                role INTEGER,
                sno TEXT UNIQUE,
                password TEXT
            )
        """
        db.execSQL(createTable)

        // 如果 User 表为空，则插入默认的负责人数据
        if (getUserCount(db) == 0) {
            insertDefaultUsers(db)
        }
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS User")
        onCreate(db)
    }

    // 检查用户是否存在
    fun isUserExist(sno: String): Boolean {
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery("SELECT * FROM User WHERE sno = ?", arrayOf(sno))
        val exists = cursor.count > 0
        cursor.close()
        return exists
    }

    // 验证学号是否是负责人
    fun isLeader(sno: String): Boolean {
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery(
            "SELECT * FROM User WHERE sno = ? AND role = 1 ", arrayOf(sno)
        )
        val exists = cursor.count > 0
        cursor.close()
        return exists
    }

    // 验证学号和密码是否匹配
    fun checkUser(sno: String, password: String): Boolean {
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery(
            "SELECT * FROM User WHERE sno = ? AND password = ?",
            arrayOf(sno, password)
        )
        val userExists = cursor.count > 0
        cursor.close()
        return userExists
    }

    // 更新用户密码
    fun updatePassword(sno: String, newPassword: String) {
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put("password", newPassword)
        db.update("User", contentValues, "sno = ?", arrayOf(sno))
    }

    // 插入用户
    fun insertUser(academy: String, club: String, name: String, role: Int, sno: String, password: String) {
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put("academy", academy)
        contentValues.put("club", club)
        contentValues.put("name", name)
        contentValues.put("role", role)
        contentValues.put("sno", sno)
        contentValues.put("password", password)
        db.insert("User", null, contentValues)
    }

    // 预填充一条负责人数据
    private fun insertDefaultUsers(db: SQLiteDatabase) {
        val contentValues = ContentValues()

        // 插入负责人信息，只有一条记录，学号为 "1"
        contentValues.put("academy", "计算机科学学院")
        contentValues.put("club", "计算机科学协会")
        contentValues.put("name", "1")
        contentValues.put("role", 1)  // 1 表示负责人
        contentValues.put("sno", "1")   // 学号
        contentValues.put("password", "1")  // 密码
        db.insert("User", null, contentValues)

        contentValues.clear()  // 清空之前的数据
        contentValues.put("academy", "物理与电子科技学院")
        contentValues.put("club", "计算机科学协会")
        contentValues.put("name", "2")
        contentValues.put("role", 2)  // 1 表示负责人
        contentValues.put("sno", "2")   // 学号
        contentValues.put("password", "2")  // 密码
        db.insert("User", null, contentValues)

        contentValues.clear()  // 清空之前的数据
        contentValues.put("academy", "物理与电子科技学院")
        contentValues.put("club", "ERP协会")
        contentValues.put("name", "3")
        contentValues.put("role", 2)  // 1 表示负责人
        contentValues.put("sno", "3")   // 学号
        contentValues.put("password", "3")  // 密码
        db.insert("User", null, contentValues)

    }

    // 获取表中用户数量
    private fun getUserCount(db: SQLiteDatabase): Int {
        val cursor = db.rawQuery("SELECT COUNT(*) FROM User", null)
        cursor.moveToFirst()
        val count = cursor.getInt(0)
        cursor.close()
        return count
    }
    fun getUserBySno(sno:String):Cursor{
        val db = this.readableDatabase
        val cursor = db.rawQuery("SELECT * FROM User WHERE sno = ?", arrayOf(sno))
        return cursor
    }
    fun updateAll(sno:String,academy: String?, club: String?, password: String?){
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put("sno",sno)
        academy?.let { contentValues.put("academy", it) }
        club?.let { contentValues.put("club", it) }
        password?.let { contentValues.put("password", it) }
        db.update("User",contentValues,"sno = ?", arrayOf(sno))
    }
    fun getUserByClub(club: String):Cursor{
        val db=this.readableDatabase
        val cursor:Cursor=db.rawQuery("SELECT * FROM User WHERE club = ?", arrayOf(club))
        return cursor
    }
}

